/*
This code produces numbers for the distributional analysis
*/


use "${datadir}/distributional_data", clear

//###########
//Simple percentange of ownership match rate

gen total_own_share = o_p20 + o_p40 + o_p60 + o_p80 + o_p90 + o_p95 + o_p99 + o_p100

summarize total_own_share, detail

//Weighted by amount of PPP

replace forgivenessamount_first = 0 if forgivenessamount_first == .
replace forgivenessamount_second = 0 if forgivenessamount_second == .
gen total_ppp = round(forgivenessamount_first + forgivenessamount_second, 0)
summarize total_own_share [aweight = total_ppp] , detail


//############
//PPP allocation among the workers
gen ppp_to_workers = forgivenessamount_first * 0.52

replace totcount = jobsreported_first if totcount == .


//Allow for differential allocation by firm size
gen ppp_to_workers_sized = (.53)*forgivenessamount_first if totcount >= 1 & totcount <= 9
replace ppp_to_workers_sized = (.68)*forgivenessamount_first if totcount >= 10 & totcount < 50
replace ppp_to_workers_sized = (.55)*forgivenessamount_first if totcount >= 50 & totcount < 100
replace ppp_to_workers_sized = (.59)*forgivenessamount_first if totcount >= 100 & totcount < 250
replace ppp_to_workers_sized = (.69)*forgivenessamount_first if totcount >= 250

//Lump Sum - based on employees in each bin
foreach ptile in c20 c40 c60 c80 c90 c95 c99 c100 {
	gen ppp_w_l_`ptile' = (w_`ptile' / totcount) * ppp_to_workers
	gen ppp_w_s_l_`ptile' = (w_`ptile' / totcount ) * ppp_to_workers_sized
}


//Proportional - based on amount of wages paid to each bin
foreach ptile in p20 p40 p60 p80 p90 p95 p99 p100 {
	gen ppp_w_p_`ptile' = w_`ptile' * ppp_to_workers
	gen ppp_w_s_p_`ptile' = w_`ptile' * ppp_to_workers_sized
}

//Proportional with a Maximum of either the total wages paid to the bin or $100k paid to each employee

forvalues ptile = 20(20)80 {
	gen w_avg_p`ptile' = totwages *(w_p`ptile') / w_c`ptile'
	gen top_code_p`ptile' = min(w_avg_p`ptile' * w_c`ptile', 1e+5*w_c`ptile')
}

gen w_avg_p90 = totwages *(w_p90) / w_c90
gen top_code_p90 = min(w_avg_p90 * w_c90, 1e+5*w_c90)

gen w_avg_p95 = totwages *(w_p95) / w_c95
gen top_code_p95 = min(w_avg_p95 * w_c90, 1e+5*w_c95)

gen w_avg_p99 = totwages *(w_p99) / w_c99
gen top_code_p99 = min(w_avg_p99 * w_c99, 1e+5*w_c99)

gen w_avg_p100 = totwages *(w_p100) / w_c100
gen top_code_p100 = min(w_avg_p100 * w_c100, 1e+5*w_c100)

//Reassign all wages based upon new fractions after top-coding

gen top_code_total = top_code_p20 + top_code_p40 + top_code_p60 + top_code_p80 + ///
	top_code_p90 + top_code_p95 + top_code_p99 + top_code_p100

foreach ptile in p20 p40 p60 p80 p90 p95 p99 p100 {
	gen ppp_w_t_p_`ptile' = (top_code_`ptile' / top_code_total) * ppp_to_workers
}

//Lump sum top coded at total wages paid to the income bin

forvalues ptile = 20(20)80 {
	gen ppp_w_l_c_c`ptile' = (w_c`ptile' / totcount) * ppp_to_workers
	replace ppp_w_l_c_c`ptile' = w_p`ptile'*totwages if ppp_w_l_c_c`ptile' > w_p`ptile'*totwages 
}

gen ppp_w_l_c_c90 = (w_c90 / totcount) * ppp_to_workers
replace ppp_w_l_c_c90 = w_p90*totwages if ppp_w_l_c_c90 > w_p90*totwages 

gen ppp_w_l_c_c95 = (w_c95 / totcount) * ppp_to_workers
	replace ppp_w_l_c_c95 = w_p95*totwages if ppp_w_l_c_c95 > w_p95*totwages 

gen ppp_w_l_c_c99 = (w_c99 / totcount) * ppp_to_workers
replace ppp_w_l_c_c99 = w_p99*totwages if ppp_w_l_c_c99 > w_p99*totwages 

gen ppp_w_l_c_c100 = (w_c100 / totcount) * ppp_to_workers
replace ppp_w_l_c_c100 = w_p100*totwages if ppp_w_l_c_c100 > w_p100*totwages 


//Choose one to use for ownership and the tax effects

global dist_var "ppp_w_s_l_"
global prefix "c"
global loop_list "${prefix}20 ${prefix}40 ${prefix}60 ${prefix}80 ${prefix}90 ${prefix}95 ${prefix}99 ${prefix}100"


//############
//PPP allocation among the ownership
//gen ppp_to_owners = forgivenessamount_first * 0.38


//Allow for different allocation based on the sized regressions
gen ppp_to_owners = (.47)*forgivenessamount_first if totcount <= 9
replace ppp_to_owners = (.32)*forgivenessamount_first if totcount >= 10 & totcount < 50
replace ppp_to_owners = (.45)*forgivenessamount_first if totcount >= 50 & totcount < 100
replace ppp_to_owners = (.41)*forgivenessamount_first if totcount >= 100 & totcount < 250
replace ppp_to_owners = (.31)*forgivenessamount_first if totcount >= 250


//Distribute dollars of PPP loans to ownership bins
foreach ptile in p20 p40 p60 p80 p90 p95 p99 p100 {
	gen ppp_o_`ptile' = o_`ptile' * ppp_to_owners
}

//Add scale ups for totals for owners and employees based on business type reported on the PPP application

gen cond_bus_type = businesstype_first
replace cond_bus_type = "non-profit" if inlist(businesstype_first, "501(c)3 â Non Profit", "501(c)19 â Non Profit Veterans","501(c)3 â Non Profit", "501(c)6 â Non Profit Membership", "Non-Profit Childcare Center", "Non-Profit Organization", "Tribal Concerns", "501(c) â Non Profit except 3,4,6,")
replace cond_bus_type = "sole" if inlist(businesstype_first, "Independent Contractors", "Qualified Joint-Venture (spouses)", "Rollover as Business Start-Ups (ROB", "Self-Employed Individuals", "Single Member LLC", "Sole Proprietorship")
replace cond_bus_type = "partnership" if inlist(businesstype_first, "Cooperative", "Housing Co-op", "Joint Venture", "Limited Liability Partnership", "Partnership")
replace cond_bus_type = "c_corp" if inlist(businesstype_first, "Corporation")
replace cond_bus_type = "s_corp" if inlist(businesstype_first, "Subchapter S Corporation")
replace cond_bus_type = "ownership" if inlist(businesstype_first, "Employee Stock Ownership Plan(ESOP)", "Tenant in Common", "Trust")
replace cond_bus_type = "prof_assoc" if inlist(businesstype_first, "Professional Association")
replace cond_bus_type = "other" if inlist(businesstype_first, "")
replace cond_bus_type = "llc" if inlist(businesstype_first, "Limited  Liability Company(LLC)")

merge m:1 cond_bus_type using "${datadir}/scale_ups/owners", nogen

merge m:1 cond_bus_type using "${datadir}/scale_ups/w2", nogen

//totals

//For the owner numbers (dollars going to each)

preserve
gcollapse (rawsum) forgivenessamount_first ppp_to_owners (sum) ppp_o_p* if ppp_to_owners != 0 [pw=dollar_own_wt]
export excel "${outdir}/total_dist_nat.xlsx", sheet("owners",replace) firstrow(variables)
restore

//For the worker numbers (dollars going)
preserve
gcollapse (rawsum) forgivenessamount_first ppp_to_workers ppp_to_workers_sized (sum) ppp_w_l* ppp_w_p* /// 
	 ppp_w_s_l* ppp_w_s_p* ppp_w_t* [pw=dollar_w2_wt]
export excel "${outdir}/distrib/total_dist_nat.xlsx", sheet("workers",replace) firstrow(variables)
restore



//Now collapse by entity type
preserve
gcollapse (rawsum) forgivenessamount_first ppp_to_owners (sum) ppp_o_p* if ppp_to_owners != 0 [pw=dollar_own_wt],by(ent_type)
export excel "${outdir}/distrib/total_dist_nat_ent.xlsx", sheet("owners",replace) firstrow(variables)
restore

//For the worker numbers
preserve
gcollapse (rawsum) forgivenessamount_first ppp_to_workers ppp_to_workers_sized (sum) ppp_w_l* ppp_w_p* /// 
	 ppp_w_s_l* ppp_w_s_p* ppp_w_t* [pw=dollar_w2_wt], by(ent_type)
export excel "${outdir}/distrib/total_dist_nat_ent.xlsx", sheet("workers",replace) firstrow(variables)
restore


//Now do the fiscal externalities

//First do the worker's wages effects on income tax

foreach per in $loop_list {
	local rate_var = "w_r" + substr("`per'", 2,.)
	gen worker_tax_`per' = ${dist_var}`per' * `rate_var'
}

//Now do the payroll tax

foreach per in $loop_list {
	gen payroll_tax_`per' = ${dist_var}`per' * 0.153
	replace payroll_tax_`per' = 17075 + ${dist_var}`per' * 0.029 if inlist("`per'", "${prefix}95", "${prefix}99", "${prefix}100")
}

//Last is the tax expenditure from making it non-taxable and deductible

global dist_var "forgivenessamount_first*o_"
global prefix "p"
global loop_list "${prefix}20 ${prefix}40 ${prefix}60 ${prefix}80 ${prefix}90 ${prefix}95 ${prefix}99 ${prefix}100"

foreach per in $loop_list {
	local rate_var = "o_r" + substr("`per'", 2,.)
	gen owner_tax_exp_`per' = ${dist_var}`per' * `rate_var'
	replace owner_tax_exp_`per' = /*${dist_var}`per' * `rate_var' + */ ${dist_var}`per' * .21 if ent_type == 1
}


//Now output the tax effects


//by income group for distribution
preserve
gcollapse (rawsum) forgivenessamount_first ppp_to_owners ppp_to_workers (sum) owner_tax* [pw=dollar_own_wt]
export excel "${outdir}/distrib/tax_dist_owners.xlsx", replace firstrow(variables)
restore

//And now overall
preserve
gcollapse (rawsum) forgivenessamount_first ppp_to_owners ppp_to_workers (sum) worker_tax* payroll_tax* [pw=dollar_w2_wt]
export excel "${outdir}/distrib/tax_dist_workers.xlsx", replace firstrow(variables)
restore

